package th.co.exat.model;

import com.channgam.dbmlib.sql.MySQLJDBC;
import com.channgam.dbmlib.sql.iDataAdapter;
import java.util.ArrayList;
import java.util.List;
import th.co.exat.model.container.UserContainer;
import th.co.exat.uap.config.Config;
import th.co.exat.util.Encrypt;

public class User {

    private static iDataAdapter i;
    private static int numRows;

    public static UserContainer getByUserName(String userName) throws Exception {
        i = new MySQLJDBC(Config.getMySqlHost(),
                Config.getMySqlUsername(),
                Config.getMySqlPassword(),
                Config.getMySqlDbname());
        UserContainer uc = new UserContainer();
        i.connect();
        String sql = "SELECT * "
                + "FROM uap_mst_user "
                + "WHERE user_name = '" + userName + "' ";
        i.query(sql);

        while (i.resultNext()) {
            uc.setId(i.result().getInt("id"));
            uc.setUserName(i.result().getString("user_name"));
            uc.setUserPassword(i.result().getString("user_password"));
            uc.setUserDesc(i.result().getString("user_desc"));
        }

        i.close();
        return uc;
    }

    public static int getNumRows(UserContainer u) throws Exception  {
        i = new MySQLJDBC(Config.getMySqlHost(),
                Config.getMySqlUsername(),
                Config.getMySqlPassword(),
                Config.getMySqlDbname());
        i.connect();
        String sql = "SELECT count(*) AS c "
                + "FROM uap_mst_user "
                + "WHERE 1 ";

        if (u.getUserName() != null) {
            sql += "AND user_name LIKE '%" + u.getUserName() + "%' ";
        }

        if (u.getUserDesc() != null) {
            sql += "AND user_desc LIKE '%" + u.getUserDesc() + "%' ";
        }
        i.query(sql);

        while (i.resultNext()) {
            numRows = i.result().getInt("c");
        }

        i.close();

        return numRows;
    }

    public static List<UserContainer> getDataList() throws Exception {
        List<UserContainer> l = new ArrayList<UserContainer>();
        i = new MySQLJDBC(Config.getMySqlHost(),
                Config.getMySqlUsername(),
                Config.getMySqlPassword(),
                Config.getMySqlDbname());
        i.connect();
        String sql = "SELECT * "
                + "FROM uap_mst_user "
                + "WHERE 1 ";
        i.query(sql);

        while (i.resultNext()) {
            UserContainer u = new UserContainer();
            u.setId(i.result().getInt("id"));
            u.setUserName(i.result().getString("user_name"));
            u.setUserPassword(i.result().getString("user_password"));
            u.setUserDesc(i.result().getString("user_desc"));
            l.add(u);
        }

        i.close();
        return l;
    }

    public static List<UserContainer> getDataList(UserContainer c,
            int showRecord, int page) throws Exception {
        List<UserContainer> l = new ArrayList<UserContainer>();
        i = new MySQLJDBC(Config.getMySqlHost(),
                Config.getMySqlUsername(),
                Config.getMySqlPassword(),
                Config.getMySqlDbname());
        i.connect();

        int gotoPage = 0;
        if (page <= 0) {
            page = 1;
        }

        gotoPage = (page - 1) * showRecord;
        String sql = "SELECT * "
                + "FROM uap_mst_user "
                + "WHERE 1 ";
        if (c.getUserName() != null) {
            sql += "AND user_name LIKE '%" + c.getUserName() + "%' ";
        }

        if (c.getUserDesc() != null) {
            sql += "AND user_desc LIKE '%" + c.getUserDesc() + "%' ";
        }
        sql += " ORDER BY user_name ";
        sql += "LIMIT " + gotoPage + " , " + showRecord;
        
        i.query(sql);

        while (i.resultNext()) {
            UserContainer u = new UserContainer();
            u.setId(i.result().getInt("id"));
            u.setUserName(i.result().getString("user_name"));
            u.setUserPassword(i.result().getString("user_password"));
            u.setUserDesc(i.result().getString("user_desc"));
            l.add(u);
        }

        i.close();
        return l;
    }

    public static int insertData(UserContainer u) throws Exception {
        i = new MySQLJDBC(Config.getMySqlHost(),
                Config.getMySqlUsername(),
                Config.getMySqlPassword(),
                Config.getMySqlDbname());
        i.connect();

        String sql = "INSERT INTO uap_mst_user " +
                "(user_name , user_password , user_desc) " +
                "VALUES " +
                "('" + u.getUserName() + "'," +
                "'" + Encrypt.MD5(u.getUserPassword()) + "'," +
                "'" + u.getUserDesc() + "')";
        i.statement(sql);
        return i.execute();
    }

    public static int deleteData(String id) throws Exception {
        i = new MySQLJDBC(Config.getMySqlHost(),
                Config.getMySqlUsername(),
                Config.getMySqlPassword(),
                Config.getMySqlDbname());
        i.connect();

        String sql = "DELETE FROM uap_mst_user " +
                "WHERE id = '" + id + "'";
        i.statement(sql);
        return i.execute();
    }
}
